USE [master]
GO
/****** Object:  Database [ToolBox2]    Script Date: 4/13/2013 11:59:36 PM ******/
CREATE DATABASE [ToolBox2]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'ToolBox2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012BI\MSSQL\DATA\ToolBox2.mdf' , SIZE = 38912KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'ToolBox2_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012BI\MSSQL\DATA\ToolBox2_log.ldf' , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [ToolBox2] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [ToolBox2].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [ToolBox2] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [ToolBox2] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [ToolBox2] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [ToolBox2] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [ToolBox2] SET ARITHABORT OFF 
GO
ALTER DATABASE [ToolBox2] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [ToolBox2] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [ToolBox2] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [ToolBox2] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [ToolBox2] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [ToolBox2] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [ToolBox2] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [ToolBox2] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [ToolBox2] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [ToolBox2] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [ToolBox2] SET  DISABLE_BROKER 
GO
ALTER DATABASE [ToolBox2] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [ToolBox2] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [ToolBox2] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [ToolBox2] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [ToolBox2] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [ToolBox2] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [ToolBox2] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [ToolBox2] SET RECOVERY FULL 
GO
ALTER DATABASE [ToolBox2] SET  MULTI_USER 
GO
ALTER DATABASE [ToolBox2] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [ToolBox2] SET DB_CHAINING OFF 
GO
ALTER DATABASE [ToolBox2] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [ToolBox2] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
USE [ToolBox2]
GO
/****** Object:  User [UserTempTable]    Script Date: 4/13/2013 11:59:36 PM ******/
CREATE USER [UserTempTable] FOR LOGIN [UserTempTable] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [UserTableVar]    Script Date: 4/13/2013 11:59:36 PM ******/
CREATE USER [UserTableVar] FOR LOGIN [UserTableVar] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [UserBuildTable]    Script Date: 4/13/2013 11:59:36 PM ******/
CREATE USER [UserBuildTable] FOR LOGIN [UserBuildTable] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  DatabaseRole [PerfCouunter_Logging]    Script Date: 4/13/2013 11:59:36 PM ******/
CREATE ROLE [PerfCouunter_Logging]
GO
ALTER ROLE [db_owner] ADD MEMBER [UserTempTable]
GO
ALTER ROLE [db_owner] ADD MEMBER [UserTableVar]
GO
ALTER ROLE [db_owner] ADD MEMBER [UserBuildTable]
GO
/****** Object:  StoredProcedure [dbo].[PerfCounterDwImport_sp]    Script Date: 4/13/2013 11:59:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[PerfCounterDwImport_sp]
as
--declare @ImportBookmark datetime
--select	@ImportBookmark = dateadd( mi , -1 , getdate() )

truncate table PerfCounterStage

insert	PerfCounterStage(
		PerfCounterId , LogDate , ServerName , CategoryName , CounterName , InstanceName , Value
)
select	PerfCounterId , LogDate , ServerName , CategoryName , CounterName , InstanceName , Value
from	PerfCounter
--where	LogDate	<	@ImportBookmark

delete	PerfCounter
--where	LogDate	<	@ImportBookmark

insert	PerfCounterDimServerName(
		ServerName
)
select	distinct 
		ServerName
from	PerfCounterStage
where	ServerName not in ( select ServerName from PerfCounterDimServerName )

insert	PerfCounterDimCategoryName(
		CategoryName
)
select	distinct 
		CategoryName
from	PerfCounterStage
where	CategoryName not in ( select CategoryName from PerfCounterDimCategoryName )

insert	PerfCounterDimCounterName(
		CounterName
)
select	distinct 
		CounterName
from	PerfCounterStage
where	CounterName not in ( select CounterName from PerfCounterDimCounterName )

insert	PerfCounterDimInstanceName(
		InstanceName
)
select	distinct 
		InstanceName
from	PerfCounterStage
where	InstanceName not in ( select InstanceName from PerfCounterDimInstanceName )

insert	PerfCounterDimLogDate(
		LogDate
)
select	distinct 
		LogDate
from	PerfCounterStage
where	LogDate not in ( select LogDate from PerfCounterDimLogDate )

insert	PerfCounterFact(
		PerfCounterId , LogDateId , ServerNameId , CategoryNameId , CounterNameId , InstanceNameId , Value
)
select	pc.PerfCounterId , ld.LogDateId , sn.ServerNameId , cnt.CategoryNameId , ctr.CounterNameId , ins.InstanceNameId , pc.Value
from	PerfCounterStage							pc
		inner join PerfCounterDimServerName		sn	on	sn.ServerName		=	pc.ServerName
		inner join PerfCounterDimCategoryName	cnt	on	cnt.CategoryName	=	pc.CategoryName
		inner join PerfCounterDimCounterName	ctr	on	ctr.CounterName		=	pc.CounterName
		inner join PerfCounterDimInstanceName	ins	on	ins.InstanceName	=	pc.InstanceName
		inner join PerfCounterDimLogDate		ld	on	ld.LogDate			=	pc.LogDate

GO
/****** Object:  StoredProcedure [dbo].[PerfCounterInsert_sp]    Script Date: 4/13/2013 11:59:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[PerfCounterInsert_sp]
	@ServerName		varchar(100)
,	@CategoryName	varchar(100)
,	@CounterName	varchar(100)
,	@InstanceName	varchar(500)
,	@Value			float
as
insert	PerfCounter( 
		LogDate , ServerName , CategoryName , CounterName , InstanceName , Value 
)
values	(
		getdate() , @ServerName , @CategoryName , @CounterName , @InstanceName , @Value 
)

GO
/****** Object:  StoredProcedure [dbo].[ProcessInfoLog_LogData_sp]    Script Date: 4/13/2013 11:59:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE proc [dbo].[ProcessInfoLog_LogData_sp]
as
declare	@LogDate	smalldatetime
,	@LogDateTimeID	int
select	@LogDate	= getdate()

insert	LogDateTime ( Yr , Qt , Mo , Wk , Dy , Hr , Mn )
select	datepart( yyyy , @LogDate )
,	'Quarter ' + convert( char(1) , datepart( qq , @LogDate ) )
,	case	datepart( mm , @LogDate )
		when	1	then	'01 - Jan'
		when	2	then	'02 - Feb'
		when	3	then	'03 - Mar'
		when	4	then	'04 - Apr'
		when	5	then	'05 - May'
		when	6	then	'06 - Jun'
		when	7	then	'07 - Jul'
		when	8	then	'08 - Aug'
		when	9	then	'09 - Sep'
		when	10	then	'10 - Oct'
		when	11	then	'11 - Nov'
		when	12	then	'12 - Dec'
		else			'???'
	end
,	datepart( wk , @LogDate )
,	datepart( dd , @LogDate )
,	datepart( hh , @LogDate )
,	datepart( mi , @LogDate )
select	@LogDateTimeID = Scope_Identity()
				
insert	ProcessInfoLog (
	[LogDateTimeID]
,	[ProcessID]
,	[Host]
,	[Database]
,	[User]
,	[Application]
,	[ContextID]
,	[LoginTime]
,	[CPU]
,	[PhysicalIO]
,	[DMV_CPU]
,	[DMV_PIO]
,	[Reads]
,	[Writes]
,	[LogicalReads]
)
select	@LogDateTimeID -- getdate()
,		s.session_id as spid
,		isnull(host_name,'') as hostname
,		case	when s.database_id = 0	then	'no database context'
				else							db_name(s.database_id)
		end as db_name
,		case	when s.session_id > 6	then	s.login_name
				else							'system'
		end as user_name
,		isnull(s.program_name,'') as program_name
,		convert(int,s.context_info ) as ecid
,		s.login_time

,		sum(s.cpu_time + isnull(r.cpu_time,0))								as cpu
,		sum(s.reads + s.writes + isnull(r.reads,0) + isnull(r.writes,0))	as physical_io

,		sum(s.cpu_time + isnull(r.cpu_time,0))								as dmv_cpu
,		sum(s.reads + s.writes + isnull(r.reads,0) + isnull(r.writes,0))	as dmv_physical_io
,		sum(s.reads + isnull(r.reads,0))									as reads
,		sum(s.writes + isnull(r.writes,0))									as writes
,		sum(s.logical_reads + isnull(r.logical_reads,0))					as logical_reads

from		sys.dm_exec_sessions	s with (NOLOCK)
left join	sys.dm_exec_requests	r with (NOLOCK) on	r.session_id	=	s.session_id
--full join	master.dbo.sysprocesses p with (NOLOCK)	on	p.spid			=	s.session_id
--													and	p.ecid			=	convert(int,s.context_info )
group by
		s.session_id --as spid
,		isnull(host_name,'') --as hostname
,		case	when s.database_id = 0	then	'no database context'
				else							db_name(s.database_id)
		end --as db_name
,		case	when s.session_id > 6	then	s.login_name
				else							'system'
		end --as user_name
,		isnull(s.program_name,'') --as program_name
,		convert(int,s.context_info ) --as ecid
,		s.login_time




GO
/****** Object:  Table [dbo].[_ProcessInfoLog]    Script Date: 4/13/2013 11:59:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[_ProcessInfoLog](
	[ProcessInfoLogID] [bigint] IDENTITY(-9223372036854775808,1) NOT NULL,
	[LogDateTimeID] [bigint] NOT NULL,
	[ProcessID] [int] NOT NULL,
	[Host] [varchar](100) NULL,
	[Database] [varchar](100) NOT NULL,
	[User] [varchar](50) NOT NULL,
	[Application] [varchar](255) NULL,
	[ContextID] [int] NULL,
	[LoginTime] [datetime] NOT NULL,
	[CPU] [int] NULL,
	[PhysicalIO] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[DemoTable]    Script Date: 4/13/2013 11:59:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DemoTable](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[data] [varchar](8000) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[LogDateTime]    Script Date: 4/13/2013 11:59:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LogDateTime](
	[LogDateTimeID] [int] IDENTITY(-2147483648,1) NOT NULL,
	[Yr] [smallint] NULL,
	[Qt] [varchar](10) NULL,
	[Mo] [varchar](10) NULL,
	[Wk] [tinyint] NULL,
	[Dy] [tinyint] NULL,
	[Hr] [tinyint] NULL,
	[Mn] [tinyint] NULL,
 CONSTRAINT [PK_LogDate] PRIMARY KEY CLUSTERED 
(
	[LogDateTimeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[PerfCounter]    Script Date: 4/13/2013 11:59:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PerfCounter](
	[PerfCounterId] [int] IDENTITY(-2147483648,1) NOT NULL,
	[LogDate] [datetime] NULL,
	[ServerName] [varchar](100) NULL,
	[CategoryName] [varchar](100) NULL,
	[CounterName] [varchar](100) NULL,
	[InstanceName] [varchar](500) NULL,
	[Value] [float] NULL,
 CONSTRAINT [PK_PerfCounter] PRIMARY KEY CLUSTERED 
(
	[PerfCounterId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[PerfCounterDimCategoryName]    Script Date: 4/13/2013 11:59:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PerfCounterDimCategoryName](
	[CategoryNameId] [smallint] IDENTITY(1,1) NOT NULL,
	[CategoryName] [varchar](100) NULL,
 CONSTRAINT [PK_PerfCounterDimCategoryName] PRIMARY KEY CLUSTERED 
(
	[CategoryNameId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[PerfCounterDimCounterName]    Script Date: 4/13/2013 11:59:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PerfCounterDimCounterName](
	[CounterNameId] [smallint] IDENTITY(1,1) NOT NULL,
	[CounterName] [varchar](100) NULL,
 CONSTRAINT [PK_CounterDimCounterName] PRIMARY KEY CLUSTERED 
(
	[CounterNameId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[PerfCounterDimInstanceName]    Script Date: 4/13/2013 11:59:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PerfCounterDimInstanceName](
	[InstanceNameId] [smallint] IDENTITY(1,1) NOT NULL,
	[InstanceName] [varchar](500) NULL,
 CONSTRAINT [PK_CounterDimInstanceName] PRIMARY KEY CLUSTERED 
(
	[InstanceNameId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[PerfCounterDimLogDate]    Script Date: 4/13/2013 11:59:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PerfCounterDimLogDate](
	[LogDateId] [int] IDENTITY(-2147483648,1) NOT NULL,
	[LogDate] [datetime] NULL,
 CONSTRAINT [PK_PerfCounterDimLogDate] PRIMARY KEY CLUSTERED 
(
	[LogDateId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[PerfCounterDimServerName]    Script Date: 4/13/2013 11:59:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PerfCounterDimServerName](
	[ServerNameId] [smallint] IDENTITY(1,1) NOT NULL,
	[ServerName] [varchar](100) NULL,
 CONSTRAINT [PK_CounterDimServerName] PRIMARY KEY CLUSTERED 
(
	[ServerNameId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[PerfCounterFact]    Script Date: 4/13/2013 11:59:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PerfCounterFact](
	[PerfCounterId] [int] NOT NULL,
	[LogDateId] [int] NULL,
	[ServerNameId] [smallint] NULL,
	[CategoryNameId] [smallint] NULL,
	[CounterNameId] [smallint] NULL,
	[InstanceNameId] [smallint] NULL,
	[Value] [float] NULL,
 CONSTRAINT [PK_PerfCounterFact] PRIMARY KEY CLUSTERED 
(
	[PerfCounterId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[PerfCounterStage]    Script Date: 4/13/2013 11:59:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PerfCounterStage](
	[PerfCounterId] [int] NOT NULL,
	[LogDate] [datetime] NULL,
	[ServerName] [varchar](100) NULL,
	[CategoryName] [varchar](100) NULL,
	[CounterName] [varchar](100) NULL,
	[InstanceName] [varchar](500) NULL,
	[Value] [float] NULL,
 CONSTRAINT [PK_perfcounterstage] PRIMARY KEY CLUSTERED 
(
	[PerfCounterId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ProcessInfoLog]    Script Date: 4/13/2013 11:59:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ProcessInfoLog](
	[ProcessInfoLogID] [bigint] IDENTITY(-9223372036854775808,1) NOT NULL,
	[LogDateTimeID] [bigint] NOT NULL,
	[ProcessID] [int] NOT NULL,
	[Host] [varchar](100) NULL,
	[Database] [varchar](100) NOT NULL,
	[User] [varchar](50) NOT NULL,
	[Application] [varchar](255) NULL,
	[ContextID] [int] NULL,
	[LoginTime] [datetime] NOT NULL,
	[CPU] [int] NULL,
	[PhysicalIO] [int] NULL,
	[DMV_CPU] [int] NULL,
	[DMV_PIO] [int] NULL,
	[Reads] [int] NULL,
	[Writes] [int] NULL,
	[LogicalReads] [int] NULL,
 CONSTRAINT [PK_ProcessInfoLog] PRIMARY KEY CLUSTERED 
(
	[ProcessInfoLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ProcessInfoLogCubeWindow]    Script Date: 4/13/2013 11:59:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProcessInfoLogCubeWindow](
	[MinPIL] [numeric](19, 0) NOT NULL,
	[MaxPIL] [bigint] NULL
) ON [PRIMARY]

GO
/****** Object:  View [dbo].[ProcessInfoLog_vw]    Script Date: 4/13/2013 11:59:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE view [dbo].[ProcessInfoLog_vw]
as
select	pil.ProcessInfoLogID
,	pil.[LogDateTimeID]
,	pil.[ProcessID]
,	pil.[Host]
,	pil.[Database]
,	pil.[User]
,	pil.[Application]
,	pil.[ContextID]
,	pil.[LoginTime]
,	pil.[CPU]
,	pil.[PhysicalIO]
,	pil.[DMV_CPU]
,	pil.[DMV_PIO]
,	pil.[Reads]
,	pil.[Writes]
,	pil.[LogicalReads]
from	ProcessInfoLog		pil
	inner join LogDateTime	ldt	on	ldt.LogDateTimeID	=	pil.LogDateTimeID



GO
/****** Object:  View [dbo].[ProcessInfo_cube]    Script Date: 4/13/2013 11:59:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE view [dbo].[ProcessInfo_cube]
as
select	ProcessInfoLogID
,	LogDateTimeID
,	[LoginTime]
,	[ProcessID]
,	ContextID
,	convert( varchar , [LoginTime] , 126 ) + ' - ' + convert( varchar , [ProcessID] )  + ' - ' + convert( varchar , ContextID )	as Session
,	[Database]
,	Host
,	Application
,	CPU
,	case 	when		(	select	top 1 CPU
							from	ProcessInfoLog_vw
							where	[LoginTime]	=	i.[LoginTime]
							and	[ProcessID]	=	i.[ProcessID]
							and	ContextID	=	i.ContextID
							group by
								CPU , LogDateTimeID
							having	LogDateTimeID	< i.LogDateTimeID
							order by
								LogDateTimeID desc
						)	is	null	then	
						case	when	(	select	top 1 [LoginTime]
											from	ProcessInfoLog_vw
											where	[LoginTime]	=	i.[LoginTime]
											and	[ProcessID]	=	i.[ProcessID]
											and	ContextID	=	i.ContextID
											group by
												[LoginTime] , LogDateTimeID
											having	LogDateTimeID	< i.LogDateTimeID
											order by
												LogDateTimeID desc
										)
										>
										(	select	top 1 LogDateTimeID
											from	ProcessInfoLog_vw
											where	[LoginTime]	=	i.[LoginTime]
											and	[ProcessID]	=	i.[ProcessID]
											and	ContextID	=	i.ContextID
											group by
												LogDateTimeID
											having	LogDateTimeID	< i.LogDateTimeID
											order by
												LogDateTimeID desc
										)
								then	CPU
							else		0
						end
		else	
			case	when	CPU	>	(	select	top 1 CPU
										from	ProcessInfoLog_vw
										where	[LoginTime]	=	i.[LoginTime]
										and	[ProcessID]	=	i.[ProcessID]
										and	ContextID	=	i.ContextID
										group by
											CPU , LogDateTimeID
										having	LogDateTimeID	< i.LogDateTimeID
										order by
											LogDateTimeID desc
									)
					then
						CPU - 	(	select	top 1 CPU
									from	ProcessInfoLog_vw
									where	[LoginTime]	=	i.[LoginTime]
									and	[ProcessID]	=	i.[ProcessID]
									and	ContextID	=	i.ContextID
									group by
										CPU , LogDateTimeID
									having	LogDateTimeID	< i.LogDateTimeID
									order by
										LogDateTimeID desc
								)
					else		0
			end
		end
		as CPU_change
,	[PhysicalIO]
,	case 	when				(	select	top 1 [PhysicalIO]
									from	ProcessInfoLog_vw
									where	[LoginTime]	=	i.[LoginTime]
									and	[ProcessID]	=	i.[ProcessID]
									and	ContextID	=	i.ContextID
									group by
										[PhysicalIO] , LogDateTimeID
									having	LogDateTimeID	< i.LogDateTimeID
									order by
										LogDateTimeID desc
								)	is	null	then	
								0
		else	
			[PhysicalIO]	 - 	(	select	top 1 [PhysicalIO]
								from	ProcessInfoLog_vw
								where	[LoginTime]	=	i.[LoginTime]
								and	[ProcessID]	=	i.[ProcessID]
								and	ContextID	=	i.ContextID
								group by
									[PhysicalIO] , LogDateTimeID
								having	LogDateTimeID	< i.LogDateTimeID
								order by
									LogDateTimeID desc
								)
	end
		as PIO_change
,	[User]
,	case 	when				(	select	top 1 [DMV_CPU]
									from	ProcessInfoLog_vw
									where	[LoginTime]	=	i.[LoginTime]
									and	[ProcessID]	=	i.[ProcessID]
									and	ContextID	=	i.ContextID
									group by
										[DMV_CPU] , LogDateTimeID
									having	LogDateTimeID	< i.LogDateTimeID
									order by
										LogDateTimeID desc
								)	is	null	then	
								0
		else	
			[DMV_CPU]	 - 	(	select	top 1 [DMV_CPU]
								from	ProcessInfoLog_vw
								where	[LoginTime]	=	i.[LoginTime]
								and	[ProcessID]	=	i.[ProcessID]
								and	ContextID	=	i.ContextID
								group by
									[DMV_CPU] , LogDateTimeID
								having	LogDateTimeID	< i.LogDateTimeID
								order by
									LogDateTimeID desc
								)
	end
		as DMV_CPU_change
,	case 	when				(	select	top 1 [DMV_PIO]
									from	ProcessInfoLog_vw
									where	[LoginTime]	=	i.[LoginTime]
									and	[ProcessID]	=	i.[ProcessID]
									and	ContextID	=	i.ContextID
									group by
										[DMV_PIO] , LogDateTimeID
									having	LogDateTimeID	< i.LogDateTimeID
									order by
										LogDateTimeID desc
								)	is	null	then	
								0
		else	
			[DMV_PIO]	 - 	(	select	top 1 [DMV_PIO]
								from	ProcessInfoLog_vw
								where	[LoginTime]	=	i.[LoginTime]
								and	[ProcessID]	=	i.[ProcessID]
								and	ContextID	=	i.ContextID
								group by
									[DMV_PIO] , LogDateTimeID
								having	LogDateTimeID	< i.LogDateTimeID
								order by
									LogDateTimeID desc
								)
	end
		as DMV_PIO_change
,	case 	when				(	select	top 1 [Reads]
									from	ProcessInfoLog_vw
									where	[LoginTime]	=	i.[LoginTime]
									and	[ProcessID]	=	i.[ProcessID]
									and	ContextID	=	i.ContextID
									group by
										[Reads] , LogDateTimeID
									having	LogDateTimeID	< i.LogDateTimeID
									order by
										LogDateTimeID desc
								)	is	null	then	
								0
		else	
			[Reads]	 - 	(	select	top 1 [Reads]
								from	ProcessInfoLog_vw
								where	[LoginTime]	=	i.[LoginTime]
								and	[ProcessID]	=	i.[ProcessID]
								and	ContextID	=	i.ContextID
								group by
									[Reads] , LogDateTimeID
								having	LogDateTimeID	< i.LogDateTimeID
								order by
									LogDateTimeID desc
								)
	end
		as Reads_change
,	case 	when				(	select	top 1 [Writes]
									from	ProcessInfoLog_vw
									where	[LoginTime]	=	i.[LoginTime]
									and	[ProcessID]	=	i.[ProcessID]
									and	ContextID	=	i.ContextID
									group by
										[Writes] , LogDateTimeID
									having	LogDateTimeID	< i.LogDateTimeID
									order by
										LogDateTimeID desc
								)	is	null	then	
								0
		else	
			[Writes]	 - 	(	select	top 1 [Writes]
								from	ProcessInfoLog_vw
								where	[LoginTime]	=	i.[LoginTime]
								and	[ProcessID]	=	i.[ProcessID]
								and	ContextID	=	i.ContextID
								group by
									[Writes] , LogDateTimeID
								having	LogDateTimeID	< i.LogDateTimeID
								order by
									LogDateTimeID desc
								)
	end
		as Writes_change
,	case 	when				(	select	top 1 [LogicalReads]
									from	ProcessInfoLog_vw
									where	[LoginTime]	=	i.[LoginTime]
									and	[ProcessID]	=	i.[ProcessID]
									and	ContextID	=	i.ContextID
									group by
										[LogicalReads] , LogDateTimeID
									having	LogDateTimeID	< i.LogDateTimeID
									order by
										LogDateTimeID desc
								)	is	null	then	
								0
		else	
			[LogicalReads]	 - 	(	select	top 1 [LogicalReads]
								from	ProcessInfoLog_vw
								where	[LoginTime]	=	i.[LoginTime]
								and	[ProcessID]	=	i.[ProcessID]
								and	ContextID	=	i.ContextID
								group by
									[LogicalReads] , LogDateTimeID
								having	LogDateTimeID	< i.LogDateTimeID
								order by
									LogDateTimeID desc
								)
	end
		as LogicalReads_change
from	ProcessInfoLog_vw		i
where	ProcessInfoLogID > (select min(MinPIL) from ProcessInfoLogCubeWindow)
and		ProcessInfoLogID <= (select max(MaxPIL) from ProcessInfoLogCubeWindow)



GO
/****** Object:  View [dbo].[PerfCounter_24h_vw]    Script Date: 4/13/2013 11:59:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[PerfCounter_24h_vw]
as
select	*
from	perfcounter (nolock)
where	logdate	>	dateadd( dd , -1 , getdate() )

GO
/****** Object:  View [dbo].[PerfCounterDw_vw]    Script Date: 4/13/2013 11:59:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE view [dbo].[PerfCounterDw_vw] as
select	pc.PerfCounterId , convert( smalldatetime , ld.LogDate ) as LogDate , sn.ServerName , cnt.CategoryName , ctr.CounterName , ins.InstanceName , pc.Value
from	PerfCounterFact							pc
		inner join PerfCounterDimServerName		sn	on	sn.ServerNameId		=	pc.ServerNameId
		inner join PerfCounterDimCategoryName	cnt	on	cnt.CategoryNameId	=	pc.CategoryNameId
		inner join PerfCounterDimCounterName	ctr	on	ctr.CounterNameId	=	pc.CounterNameId
		inner join PerfCounterDimInstanceName	ins	on	ins.InstanceNameId	=	pc.InstanceNameId
		inner join PerfCounterDimLogDate		ld	on	ld.LogDateId		=	pc.LogDateId


GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [PerfCounterDimCategoryName_CategoryName]    Script Date: 4/13/2013 11:59:36 PM ******/
CREATE NONCLUSTERED INDEX [PerfCounterDimCategoryName_CategoryName] ON [dbo].[PerfCounterDimCategoryName]
(
	[CategoryName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [PerfCounterDimCounterName_CounterName]    Script Date: 4/13/2013 11:59:36 PM ******/
CREATE NONCLUSTERED INDEX [PerfCounterDimCounterName_CounterName] ON [dbo].[PerfCounterDimCounterName]
(
	[CounterName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [PerfCounterDimInstanceName_InstanceName]    Script Date: 4/13/2013 11:59:36 PM ******/
CREATE NONCLUSTERED INDEX [PerfCounterDimInstanceName_InstanceName] ON [dbo].[PerfCounterDimInstanceName]
(
	[InstanceName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [PerfCounterDimLogDate_LogDate]    Script Date: 4/13/2013 11:59:36 PM ******/
CREATE NONCLUSTERED INDEX [PerfCounterDimLogDate_LogDate] ON [dbo].[PerfCounterDimLogDate]
(
	[LogDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [PerfCounterDimServerName_ServerName]    Script Date: 4/13/2013 11:59:36 PM ******/
CREATE NONCLUSTERED INDEX [PerfCounterDimServerName_ServerName] ON [dbo].[PerfCounterDimServerName]
(
	[ServerName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [missing_index_10_9_ProcessInfoLog]    Script Date: 4/13/2013 11:59:36 PM ******/
CREATE NONCLUSTERED INDEX [missing_index_10_9_ProcessInfoLog] ON [dbo].[ProcessInfoLog]
(
	[LogDateTimeID] ASC
)
INCLUDE ( 	[ProcessInfoLogID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [missing_index_2_1_ProcessInfoLog]    Script Date: 4/13/2013 11:59:36 PM ******/
CREATE NONCLUSTERED INDEX [missing_index_2_1_ProcessInfoLog] ON [dbo].[ProcessInfoLog]
(
	[ProcessID] ASC,
	[ContextID] ASC,
	[LoginTime] ASC,
	[LogDateTimeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
USE [master]
GO
ALTER DATABASE [ToolBox2] SET  READ_WRITE 
GO
